package models

import (
    "database/sql"
    "log"
    _ "github.com/mattn/go-sqlite3" // 导入 SQLite 驱动
)

var db *sql.DB // 声明全局数据库连接

// 初始化数据库连接
func InitDB() {
    var err error
    db, err = sql.Open("sqlite3", "./shopping.db") // 连接到 SQLite 数据库
    if err != nil {
        log.Fatal(err)
    }

    // 启用外键支持
    _, err = db.Exec("PRAGMA foreign_keys = ON;")
    if err != nil {
        log.Fatal("Failed to enable foreign keys:", err)
    }

    // 创建商品表
    createProductTableQuery := `
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT,
        price REAL,
        quantity INTEGER
    );`
    _, err = db.Exec(createProductTableQuery)
    if err != nil {
        log.Fatal("Failed to create products table:", err)
    }

    // 创建用户表
    createUserTableQuery := `
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT,
        password TEXT,
        phonenumber TEXT,
        email TEXT,
        balance REAL DEFAULT 0,            -- 用户余额，默认为0
        coupon_count INTEGER DEFAULT 0,    -- 优惠券数量，默认为0
        is_member BOOLEAN DEFAULT FALSE    -- 是否是会员，默认为FALSE
    );`
    _, err = db.Exec(createUserTableQuery)
    if err != nil {
        log.Fatal("Failed to create users table:", err)
    }

    // 创建购物车表，确保外键约束有效
    createCartTableQuery := `
    CREATE TABLE IF NOT EXISTS cart (
        user_id INTEGER,
        product_id INTEGER,
        quantity INTEGER,
        FOREIGN KEY(user_id) REFERENCES users(id),
        FOREIGN KEY(product_id) REFERENCES products(id),
        PRIMARY KEY(user_id, product_id)
    );`
    _, err = db.Exec(createCartTableQuery)
    if err != nil {
        log.Fatal("Failed to create cart table:", err)
    }

    // 创建订单表
    createOrderTableQuery := `
    CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        total_amount REAL,
        status TEXT,
        price REAL,  
        order_date DATETIME,
        FOREIGN KEY(user_id) REFERENCES users(id)
    );`
    _, err = db.Exec(createOrderTableQuery)
    if err != nil {
        log.Fatal("Failed to create orders table:", err)
    }

    // 创建订单商品表：订单和商品是多对多的关系
    createOrderItemTableQuery := `
    CREATE TABLE IF NOT EXISTS order_items (
        order_id INTEGER,
        product_id INTEGER,
        product_name TEXT,
        quantity INTEGER,
        price REAL,
        FOREIGN KEY(order_id) REFERENCES orders(id),
        FOREIGN KEY(product_id) REFERENCES products(id),
        PRIMARY KEY(order_id, product_id)
    );`
    _, err = db.Exec(createOrderItemTableQuery)
    if err != nil {
        log.Fatal("Failed to create order_items table:", err)
    }

    // 创建支付信息表
    createPaymentTableQuery := `
    CREATE TABLE IF NOT EXISTS payments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        order_id INTEGER,
        payment_method TEXT,
        payment_status TEXT,
        payment_date DATETIME,
        amount REAL,
        FOREIGN KEY(order_id) REFERENCES orders(id)
    );`
    _, err = db.Exec(createPaymentTableQuery)
    if err != nil {
        log.Fatal("Failed to create payments table:", err)
    }

    log.Println("Database initialized successfully.")
}

// 关闭数据库连接
func CloseDB() {
    if err := db.Close(); err != nil {
        log.Fatal("Failed to close database:", err)
    }
    log.Println("Database connection closed.")
}
